# -*- coding: utf-8 -*-
# @Time    : 2024/09/2 22:52
# @Author  : Alvin
# @File    : DatabaseHandler.py
# @Des     : 数据库处理类
import datetime
import logging
import os
import threading
import traceback

import pymysql
from dbutils.pooled_db import PooledDB


class DatabaseHandler(object):
    _instance = None
    _lock = threading.Lock()

    def __new__(cls, *args, **kwargs):
        if os.name == 'nt':
            host = '175.24.32.188'
        else:
            host = 'localhost'
        if not cls._instance:
            with cls._lock:
                if not cls._instance:
                    cls._instance = super(DatabaseHandler, cls).__new__(cls)
                    # PyMySQL 连接池配置
                    db_pool_config = {
                        "host": host,  # 数据库服务器地址
                        "user": 'root',  # 数据库用户名
                        "password": 'dd2fa810bad51b71',  # 数据库密码
                        "database": "dy_jl_logistics",  # 要连接的数据库名
                        "port": 13306,  # 数据库端口，默认为3306
                        "charset": "utf8mb4",  # 字符集
                        "maxconnections": 20,  # 连接池允许的最大连接数
                        "mincached": 2,  # 初始化时，池中至少创建的空闲的连接，0表示不创建a
                        "maxcached": 5,  # 池中最多闲置的连接，0和None不闲置
                        "maxshared": 3,  # 池中最多共享的空闲连接数，0和None表示不共享。PS: 无用，因为pymysql和MySQLdb等不支持连接共享
                        "blocking": True,  # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
                        "maxusage": None,  # 一个连接最多可以被重复使用的次数，None表示无限制
                        "setsession": [],  # 开始会话前执行的命令列表。如：["set datestyle to 'iso', session"]
                        "ping": 0,  # ping MySQL服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always
                        "use_unicode": True,  # 是否使用unicode
                        "cursorclass": pymysql.cursors.DictCursor  # 设置返回字典格式的游标"
                    }
                    # 创建一个连接池
                    cls._instance.db_pool = PooledDB(pymysql, **db_pool_config)
        return cls._instance

    def get_connection(self):
        if not self.db_pool:
            self.__new__()
        conn = self.db_pool.connection()
        cursor = conn.cursor()
        return conn, cursor

    def __init__(self):
        pass

    def add_user(self, user_info):
        """
        新增用户
        :param user_info:
        :return:
        """
        conn, cursor = self.get_connection()
        try:
            conn.begin()
            # 构建插入语句
            sql = """
                       INSERT INTO user_info 
                           (id,user_name, user_code, mobile,balance,create_time,is_deleted) 
                           VALUES (%s, %s,%s,%s,%s,%s, %s)
                   """
            # 构建参数列表
            values = (user_info['id'], user_info['user_name'], user_info['user_code'], user_info['mobile'], user_info['balance'], user_info['create_time'], user_info['is_deleted'])
            # 执行批量插入
            cursor.execute(sql, values)
            # 提交事务
            conn.commit()
            print("add_user 保存成功")
            return True
        except Exception as e:
            conn.rollback()
            logging.error(f"执行add_user时异常:{str(e)}")
            logging.error("执行add_user时异常:\n%s" % traceback.format_exc())
            return str(e)
        finally:
            cursor.close()
            conn.close()

    def get_user_by_id(self, user_id):
        """
        获取用户信息
        :param user_id: 用户id
        :return:
        """
        conn, cursor = self.get_connection()
        try:
            sql = f"SELECT * FROM user_info WHERE is_deleted = 0 AND id = '{user_id}'"
            cursor.execute(sql)
            result = cursor.fetchone()
            return result
        except Exception as e:
            logging.error(f"执行get_user_by_id异常:{str(e)}")
            logging.error("执行get_user_by_id异常:\n%s" % traceback.format_exc())
            return None
        finally:
            cursor.close()
            conn.close()

    def get_user_list(self):
        """
        获取用户列表
        :return:
        """
        conn, cursor = self.get_connection()
        try:
            sql = "SELECT * FROM user_info WHERE is_deleted = '0'"
            cursor.execute(sql)
            result = cursor.fetchall()
            dbDataList = list(result)
            return dbDataList
        except Exception as e:
            logging.error(f"执行get_user_list异常:{str(e)}")
            logging.error("执行get_user_list异常:\n%s" % traceback.format_exc())
            return None
        finally:
            cursor.close()
            conn.close()

    def update_user_balance_by_id(self, user_id, amount):
        """
        更新用户余额
        :param user_id:
        :param amount:
        :return:
        """
        conn, cursor = self.get_connection()
        try:
            update_sql = """
                       UPDATE user_info 
                       SET balance = balance - %s 
                       WHERE id = %s 
                       AND balance >= %s  -- 确保余额足够
                    """
            values = (amount, user_id, amount)
            cursor.execute(update_sql, values)
            affected_rows = cursor.rowcount
            if affected_rows == 0:
                conn.rollback()
                raise Exception("用户余额不足，无法创建订单")
            conn.commit()
        except Exception as e:
            conn.rollback()
            if "用户余额不足" in str(e):
                raise Exception("用户余额不足，无法创建订单")
            logging.error(f"执行update_user_balance_by_id异常:{str(e)}")
            logging.error("执行update_user_balance_by_id异常:\n%s" % traceback.format_exc())
        finally:
            cursor.close()
            conn.close()

    def insert_base_order_info(self, obj):
        """
        主表base_order_info插入数据
        :param obj:base_order_info
        :return:
        """
        conn, cursor = self.get_connection()
        try:
            conn.begin()
            # 构建插入语句
            sql = """
                INSERT INTO base_order_info (
                              id,
                              userId,
                              orderId,
                              mailNo,
                              express,
                              receiveProvince,
                              receiverCity,
                              receiverRegion,
                              receiverAddressDetail,
                              receiveName,
                              receivePhone,
                              senderName,
                              senderPhone,
                              senderAddressDetail,
                              orderCost,
                              createTime,
                              updateTime)
                            VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
            """
            # 构建参数列表
            values = (obj['id'], obj['userId'], obj['orderId'], obj['mailNo'], obj['express'], obj['receiveProvince'], obj['receiverCity'], obj['receiverRegion'],
                      obj['receiverAddressDetail'], obj['receiveName'], obj['receivePhone'], obj['senderName'], obj['senderPhone'], obj['senderAddressDetail'],
                      obj['orderCost'], obj['createTime'], obj['updateTime'])
            # 执行批量插入
            cursor.execute(sql, values)
            # 提交事务
            conn.commit()
            print("insert_base_order_info 保存成功")
            return True
        except Exception as e:
            conn.rollback()
            logging.error(f"执行insert_base_order_info时异常:{str(e)}")
            logging.error("执行insert_base_order_info时异常:\n%s" % traceback.format_exc())
            return False
        finally:
            cursor.close()
            conn.close()

    def insert_sync_order_info(self, obj):
        """
        插入同步单数据表sync_order_info
        :param obj:sync_order_info
        :return:
        """
        conn, cursor = self.get_connection()
        try:
            conn.begin()
            # 构建插入语句
            sql = """
            INSERT INTO sync_order_info (
                              id,
                              orderBaseId,
                              mailNo,
                              express,
                              mailNo_gift,
                              express_gift,
                              mailNo_self,
                              pickingCode,
                              pickingAddress,
                              hasPushPickingCode,
                              hasChangedOrder,
                              statusCode,
                              statusStr,
                              yuYueShiJian,
                              logisticsInfo,
                              expressLastUpdateTime,
                              createTime,
                              updateTime,
                            pkCodeUpdateTime,
                              kdMobile)
                            VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
            """
            # 构建参数列表
            values = (obj['id'], obj['orderBaseId'], obj['mailNo'], obj['express'], obj['mailNo_gift'], obj['express_gift'], obj['mailNo_self'],
                      obj['pickingCode'], obj['pickingAddress'], obj['hasPushPickingCode'], obj['hasChangedOrder'],
                      obj['statusCode'], obj['statusStr'], obj['yuYueShiJian'], obj['logisticsInfo'], obj['expressLastUpdateTime'],
                      obj['createTime'], obj['updateTime'], obj['pkCodeUpdateTime'], obj['kdMobile'])
            # 执行批量插入
            cursor.execute(sql, values)
            # 提交事务
            conn.commit()
            print("insert_sync_order_info 保存成功")
            return True
        except Exception as e:
            conn.rollback()
            logging.error(f"执行insert_sync_order_info时异常:{str(e)}")
            logging.error("执行insert_sync_order_info时异常:\n%s" % traceback.format_exc())
        finally:
            cursor.close()
            conn.close()

    def get_mail_no_detail_by_no(self, userId, logisticsNo):
        """
        查询用户订单列表
        :param userId:
        :param logisticsNo:
        :return:
        """
        conn, cursor = self.get_connection()
        try:
            sql = f"""
                    SELECT
                        a.mailNo,
                        a.express,
                        a.receivePhone,
                        a.receiverCity,
                        a.receiverRegion,
                        a.receiverAddressDetail,
                        b.mailNo_gift,
                        b.express_gift,
                        b.hasChangedOrder,
                        b.pickingCode,
                        b.pickingAddress,
                        b.logisticsInfo,
                        b.createTime
                    FROM
                        base_order_info a
                        INNER JOIN sync_order_info b ON a.id = b.orderBaseId 
                    WHERE
                        a.userId = '{userId}' 
                        AND b.mailNo_gift Like '%{logisticsNo}%'
                """
            cursor.execute(sql)
            result = cursor.fetchall()
            dbDataList = list(result)
            return dbDataList
        except Exception as e:
            logging.error(f"执行get_user_logistics_order_list异常:{str(e)}")
            logging.error("执行get_user_logistics_order_list异常:\n%s" % traceback.format_exc())
            return None
        finally:
            cursor.close()
            conn.close()

    def update_logistics_no_picking_code(self, pickingCode, pickingAddress, mailNo_gift):
        """
        更新取件码
        :param pickingCode:
        :param pickingAddress:
        :param userId:
        :param mailNo_gift:
        :return:
        """
        conn, cursor = self.get_connection()
        try:
            pkCodeUpdateTime = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            updateSql = f"UPDATE sync_order_info SET pickingCode ='{pickingCode}',pickingAddress = '{pickingAddress}',pkCodeUpdateTime = '{pkCodeUpdateTime}' WHERE mailNo_gift = '{mailNo_gift}'"
            logging.info(f"更新取件码sql:{updateSql}")
            cursor.execute(updateSql)
            conn.commit()
            rows_affected = cursor.rowcount

            if rows_affected == 0:
                raise Exception(f"单号不存在，请检查单号是否有误，当前传入的单号为: {mailNo_gift}")
            else:
                return True, "更新成功"
        except Exception as e:
            conn.rollback()
            logging.error(f"执行update_logistics_no_picking_code异常:{str(e)}")
            logging.error("执行update_logistics_no_picking_code异常:\n%s" % traceback.format_exc())
            return False, str(e)
        finally:
            cursor.close()
            conn.close()

    def get_custom_user_info(self, userId):
        conn, cursor = self.get_connection()
        try:
            sql = f"""
                     SELECT
                         user_name,
                         user_code,
                         mobile,
                         balance
                     FROM
                         user_info
                     WHERE
                         id = '{userId}'
                  """
            logging.info(f"查询用户信息Sql:{sql}")
            cursor.execute(sql)
            result = cursor.fetchone()
            return result
        except Exception as e:
            logging.error(f"执行get_user_logistics_order_list异常:{str(e)}")
            logging.error("执行get_user_logistics_order_list异常:\n%s" % traceback.format_exc())
            return None
        finally:
            cursor.close()
            conn.close()
